natural join：当两个表，有相同的字段名，且相同字段名对应数据类型也是相同的(长度无所谓)，则可以使用！！！功能就是简化了写两个表后，还需要写 where xxx.id=xxx.id的写法 ，且自动隐藏一个多余的相同字段名！
语法：select * from 表1 natural join 表2 natural join 表3 ；
注：natural join两侧的表必须只有1个相同字段，若有多个相同字段列时，数据将不准确，因为他会匹配所有相同的列！！！
注：使用别名会报错
注：当使用natural join且打印”*“所有字段时：natural连接的最后两张表，相同的列被打印到第一列；倒数第2张表和倒数第3张表，相同的列会被打印到第二列；以此次类推
两个表使用 natural join
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56 | --where写法
SQL> select t.ename,t.deptno,tt.dname from emp t ,dept tt where t.deptno =tt.deptno;
ENAME DEPTNO DNAME
---------- ------ --------------
CLARK 10 ACCOUNTING
KING 10 ACCOUNTING
MILLER 10 ACCOUNTING
JONES 20 RESEARCH
FORD 20 RESEARCH
ADAMS 20 RESEARCH
SMITH 20 RESEARCH
SCOTT 20 RESEARCH
WARD 30 SALES
TURNER 30 SALES
ALLEN 30 SALES
JAMES 30 SALES
BLAKE 30 SALES
MARTIN 30 SALES
--natural join写法，省略别名了，也省略where
SQL> select ename,deptno,dname from emp natural join dept;
ENAME DEPTNO DNAME
---------- ------ --------------
CLARK 10 ACCOUNTING
KING 10 ACCOUNTING
MILLER 10 ACCOUNTING
JONES 20 RESEARCH
FORD 20 RESEARCH
ADAMS 20 RESEARCH
SMITH 20 RESEARCH
SCOTT 20 RESEARCH
WARD 30 SALES
TURNER 30 SALES
ALLEN 30 SALES
JAMES 30 SALES
BLAKE 30 SALES
MARTIN 30 SALES
--若使用*号，会把相同字段列自动放到第一列的！！！
SQL> select * from emp natural join dept;
DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DNAME LOC
------ ----- ---------- --------- ----- ----------- --------- --------- -------------- -------------
10 7782 CLARK MANAGER 7839 1981/6/9 2450.00 ACCOUNTING NEW YORK
10 7839 KING PRESIDENT 1981/11/17 5000.23 ACCOUNTING NEW YORK
10 7934 MILLER CLERK 7782 1982/1/23 1300.00 ACCOUNTING NEW YORK
20 7566 JONES MANAGER 7839 1981/4/2 2975.00 RESEARCH DALLAS
20 7902 FORD ANALYST 7566 1981/12/3 3000.00 RESEARCH DALLAS
20 7876 ADAMS CLERK 7788 1987/5/23 1100.00 RESEARCH DALLAS
20 7369 SMITH CLERK 7902 1980/12/17 800.00 RESEARCH DALLAS
20 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 RESEARCH DALLAS
30 7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 SALES CHICAGO
30 7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 SALES CHICAGO
30 7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 SALES CHICAGO
30 7900 JAMES CLERK 7698 1981/12/3 950.55 SALES CHICAGO
30 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 SALES CHICAGO
30 7654 MARTIN SALESMAN 7698 1981/9/28 1250.10 1400.00 SALES CHICAGO
|
3张表，其中2张使用natural join（因为只有1个相同列），另外一张其他表（没有相同类）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40 | --natural join 两张表后，再连接另外一张表
----若使用*号，会把相同字段列放到第一列的！！！追加表时使用英文逗号分割，where正常使用即可，若没有设置别名就只能是"表名.字段名"
SQL> select * from emp natural join dept ,salgrade where sal between salgrade.losal and salgrade.hisal;
DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DNAME LOC GRADE LOSAL HISAL
------ ----- ---------- --------- ----- ----------- --------- --------- -------------- ------------- ---------- ---------- ----------
10 7839 KING PRESIDENT 1981/11/17 5000.23 ACCOUNTING NEW YORK 5 3001 9999
20 7902 FORD ANALYST 7566 1981/12/3 3000.00 RESEARCH DALLAS 4 2001 3000
20 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 RESEARCH DALLAS 4 2001 3000
20 7566 JONES MANAGER 7839 1981/4/2 2975.00 RESEARCH DALLAS 4 2001 3000
30 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 SALES CHICAGO 4 2001 3000
10 7782 CLARK MANAGER 7839 1981/6/9 2450.00 ACCOUNTING NEW YORK 4 2001 3000
30 7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 SALES CHICAGO 3 1401 2000
30 7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 SALES CHICAGO 3 1401 2000
10 7934 MILLER CLERK 7782 1982/1/23 1300.00 ACCOUNTING NEW YORK 2 1201 1400
30 7654 MARTIN SALESMAN 7698 1981/9/28 1250.10 1400.00 SALES CHICAGO 2 1201 1400
30 7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 SALES CHICAGO 2 1201 1400
20 7876 ADAMS CLERK 7788 1987/5/23 1100.00 RESEARCH DALLAS 1 700 1200
30 7900 JAMES CLERK 7698 1981/12/3 950.55 SALES CHICAGO 1 700 1200
20 7369 SMITH CLERK 7902 1980/12/17 800.00 RESEARCH DALLAS 1 700 1200
--natural join 两张表后，再连接另外一张表
--另外一张表使用别名
SQL> select * from emp natural join dept ,salgrade ttt where sal between ttt.losal and ttt.hisal;
DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DNAME LOC GRADE LOSAL HISAL
------ ----- ---------- --------- ----- ----------- --------- --------- -------------- ------------- ---------- ---------- ----------
10 7839 KING PRESIDENT 1981/11/17 5000.23 ACCOUNTING NEW YORK 5 3001 9999
20 7902 FORD ANALYST 7566 1981/12/3 3000.00 RESEARCH DALLAS 4 2001 3000
20 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 RESEARCH DALLAS 4 2001 3000
20 7566 JONES MANAGER 7839 1981/4/2 2975.00 RESEARCH DALLAS 4 2001 3000
30 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 SALES CHICAGO 4 2001 3000
10 7782 CLARK MANAGER 7839 1981/6/9 2450.00 ACCOUNTING NEW YORK 4 2001 3000
30 7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 SALES CHICAGO 3 1401 2000
30 7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 SALES CHICAGO 3 1401 2000
10 7934 MILLER CLERK 7782 1982/1/23 1300.00 ACCOUNTING NEW YORK 2 1201 1400
30 7654 MARTIN SALESMAN 7698 1981/9/28 1250.10 1400.00 SALES CHICAGO 2 1201 1400
30 7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 SALES CHICAGO 2 1201 1400
20 7876 ADAMS CLERK 7788 1987/5/23 1100.00 RESEARCH DALLAS 1 700 1200
30 7900 JAMES CLERK 7698 1981/12/3 950.55 SALES CHICAGO 1 700 1200
20 7369 SMITH CLERK 7902 1980/12/17 800.00 RESEARCH DALLAS 1 700 1200
|
3张表相连：第一张表和第二张表（有1个相同列），第二张表和第三张表（有1个相同列）...
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 | --最后两张表，相同的列被打印到第一列；倒数第2张表和倒数第3张表，相同的列会被打印到第二列；以此次类推
--bonus和 dept，是natural join最后一对：相同的列是job，打印在第一列！
--dept和emp，是natural join倒数第二对：相同的类是deptno，打印在第二列
SQL> SELECT * FROM EMP NATURAL JOIN DEPT NATURAL JOIN BONUS;
JOB DEPTNO EMPNO ENAME MGR HIREDATE SAL COMM DNAME LOC RULS
--------- ------ ----- ---------- ----- ----------- --------- --------- -------------- ------------- ---------
CLERK 20 7369 SMITH 7902 1980/12/17 800.00 RESEARCH DALLAS C
SALESMAN 30 7499 ALLEN 7698 1981/2/20 1600.00 300.00 SALES CHICAGO S
SALESMAN 30 7521 WARD 7698 1981/2/22 1250.00 500.00 SALES CHICAGO S
MANAGER 20 7566 JONES 7839 1981/4/2 2975.00 RESEARCH DALLAS M
SALESMAN 30 7654 MARTIN 7698 1981/9/28 1250.10 1400.00 SALES CHICAGO S
MANAGER 30 7698 BLAKE 7839 1981/5/1 2850.00 SALES CHICAGO M
MANAGER 10 7782 CLARK 7839 1981/6/9 2450.00 ACCOUNTING NEW YORK M
ANALYST 20 7788 SCOTT 7566 1987/4/19 3000.00 RESEARCH DALLAS A
PRESIDENT 10 7839 KING 1981/11/17 5000.23 ACCOUNTING NEW YORK P
SALESMAN 30 7844 TURNER 7698 1981/9/8 1500.00 0.00 SALES CHICAGO S
CLERK 20 7876 ADAMS 7788 1987/5/23 1100.00 RESEARCH DALLAS C
CLERK 30 7900 JAMES 7698 1981/12/3 950.55 SALES CHICAGO C
ANALYST 20 7902 FORD 7566 1981/12/3 3000.00 RESEARCH DALLAS A
CLERK 10 7934 MILLER 7782 1982/1/23 1300.00 ACCOUNTING NEW YORK C
|